from PyQt5 import QtWidgets, QtCore
from PyQt5.QtWidgets import QFileDialog, QMessageBox
from template.Ui_MainWindow import Ui_MainWindow
from task.AsyncTask import AsyncTask
import sys
import xlrd
import os
from Model10 import ImportModel as ImportModel10, ExportModel as ExportModel10
from PyQt5.QtGui import QBrush, QColor
import pymssql
import xml.etree.ElementTree as ET
import app_rc


class MainWindow(QtWidgets.QMainWindow, Ui_MainWindow):
    def __init__(self, parent):
        super(MainWindow, self).__init__()
        self.setupUi(self)
        self.btn_browser.clicked.connect(self.select_path)
        self.btn_start_deal.clicked.connect(self.start_deal)
        self.server = self.port = self.user = self.password = self.database = ''  # 数据库的地址
        self.app_init()
        self.parent = parent

    def select_path(self):
        """选择文件路径"""
        files, file_type = QFileDialog.getOpenFileNames(None, "选择要处理的文件", '/', "Excel Files (*.xlsx *.xls)")
        self.lw_paths.clear()
        self.lw_paths.addItems(files)

    def start_deal(self):
        """开始循环读取文件"""
        count = self.lw_paths.count()
        if count == 0:
            QMessageBox.warning(self.centralwidget,
                                "温馨提示",
                                "您还没有选择要处理的文件！",
                                QMessageBox.Ok)
        paths = []
        for i in range(0, count):
            paths.append(self.lw_paths.item(i).text())
        self.set_enabled(False)
        task = AsyncTask.create(lambda t: self.start_read(t, paths), lambda e: self.set_exception(e))
        task.signal_progress.connect(self.set_progress)
        task.signal.connect(lambda: self.set_enabled(True))
        task.start()

    def set_enabled(self, enabled):
        self.btn_browser.setEnabled(enabled)
        # self.lw_paths.setEnabled(enabled)
        self.btn_start_deal.setEnabled(enabled)

    FLAG_COMPLETE = "(完成)"
    FLAG_FAILED = "(出错)"

    def start_read(self, task, paths):
        count = len(paths)
        for i in range(0, count):
            try:
                if MainWindow.FLAG_COMPLETE in paths[i] or MainWindow.FLAG_FAILED in paths[i]:
                    continue
                self.read(task, i, paths)
                task.signal_progress.emit(i, 0, MainWindow.FLAG_COMPLETE)
            except Exception as e:
                task.signal_progress.emit(i, 0, str(e))
                task.signal_exception.emit(e)
        task.signal.emit()

    def read(self, task, i, paths):
        result = []
        f_name = os.path.basename(paths[i])
        if "_" in f_name:
            data_type = f_name.split('_')[1]
        elif "-" in f_name:
            data_type = f_name.split('-')[1]
        else:
            task.signal_progress.emit(i, 0, "文件名格式不正确，例如: 10002100_I/E.xlsx，编码和进出口类型之间用_或-分割")
            return
        wb = xlrd.open_workbook(paths[i])
        file_error_count = 0  # 文件中数据的条数
        for sheet in wb.sheets():
            if sheet.ncols > 1 and sheet.nrows > 1:  # 大于1才继续处理
                headers = sheet.row_values(0)  # 读取文件的表头
                ci = self.find_header_index(headers, ["日期", "申报日期"])
                date = sheet.cell_value(1, ci)
                f_row = 1
                if 'Date' in date:  # 跳过副表头
                    f_row = 2
                for ri in range(f_row, sheet.nrows):  # 循环读取每行的数据
                    model = None
                    if data_type == 'I':
                        model = ImportModel10()
                    elif data_type == 'E':
                        model = ExportModel10()
                    if model is None:
                        continue
                    props = dir(model)
                    has_error = False  # 这行数据是否有错
                    for prop in props:
                        if "__" in prop:
                            continue
                        names = [prop]
                        if prop == "数量单位":
                            names.extend(["申报数量单位"])
                        elif prop == "贸易方式":
                            names.extend(["贸易类型"])
                        elif prop == "Email":
                            names.extend(["邮件"])
                        ci = MainWindow.find_header_index(headers, names)
                        if ci == -1:
                            if data_type == "I":
                                if prop == "商品编码_8" and "商品编码" in headers:
                                    ci = headers.index("商品编码")
                                elif prop == "商品编码_2" and "商品编码" in headers:
                                    ci = headers.index("商品编码") + 1
                        cell_val = None
                        if ci != -1:
                            cell_type = sheet.cell_type(ri, ci)
                            cell_val = sheet.cell_value(ri, ci)
                            if cell_type == 3:  # 日期类型
                                cell_val = xlrd.xldate.xldate_as_datetime(cell_val, 0).strftime('%Y/%m/%d')
                            elif cell_type == 2 and cell_val % 1 == 0:  # 数字类型且是整数
                                cell_val = int(cell_val)
                            setattr(model, prop, cell_val)
                            print(prop, ci, cell_val, cell_type)
                        if prop in ["申报单价", "申报总价", "美元单价", "美元总价", "申报数量", "法定重量", "人民币总价", "毛重",
                                    "净重"]:  # float 类型的数据，验证串行
                            if cell_val and str(cell_val).upper() != 'NULL':  # 不是空或数字，表示串行了
                                try:
                                    float(cell_val)
                                except ValueError as e:
                                    print(str(e))
                                    has_error = True
                                    file_error_count += 1
                                    break
                    if has_error:  # 标记文件出错
                        task.signal_progress.emit(i, ri, MainWindow.FLAG_FAILED)
                    elif file_error_count == 0:
                        result.append(model)  # 将数据加入到待写入列表
        if file_error_count == 0:
            self.write2sql(task, i, result)  # 将数据写入数据库

    def write2sql(self, task, i, result):
        """将数据模型列表写入数据库"""
        conn = None
        try:
            conn = pymssql.connect(server=self.server,
                                   port=self.port,
                                   user=self.user,
                                   password=self.password,
                                   database=self.database)
            cursor = conn.cursor()
            wc = 0
            for model in result:
                cursor.execute(model.get_sql__())
                wc += 1
                print("成功写入一条记录, 成功总数：%d" % wc)
            cursor.close()
            conn.commit()
        except Exception as e:
            task.signal_exception.emit(e)
            task.signal_progress.emit(i, 0, str(e))
        finally:
            if conn:
                conn.close()

    @staticmethod
    def find_header_index(all_header, names):
        """查找表头的索引"""
        index = -1
        for name in names:
            if name in all_header:
                index = all_header.index(name)
                break
        return index

    def set_progress(self, i, ci, msg):
        item = self.lw_paths.item(i)
        brush = QBrush()
        if msg == MainWindow.FLAG_COMPLETE:
            brush.setColor(QColor().fromRgba(0xff000000))
        else:
            brush.setColor(QColor().fromRgba(0xffff0000))
            log_path = os.path.basename(self.lw_paths.item(i).text()) + ".log"
            if msg == MainWindow.FLAG_FAILED:
                self.write_log(log_path, "第%d行数据出错，读取失败。" % (ci + 1))
            else:
                self.write_log(log_path, msg)
            msg = MainWindow.FLAG_FAILED
        if MainWindow.FLAG_FAILED not in item.text():
            item.setForeground(brush)
            item.setText("%s%s" % (msg, item.text()))

    @staticmethod
    def write_log(log_path, msg):
        log_path = "%s/logs/%s" % (os.getcwd(), log_path)
        log = open(log_path, 'a')
        log.write(msg + os.linesep)
        log.close()

    def set_exception(self, msg):
        print(self.objectName(), msg)

    @staticmethod
    def print_create_table_sql():
        print(ImportModel10().get_create_table_sql__())
        print(ExportModel10().get_create_table_sql__())

    def app_init(self):
        path = os.getcwd() + "/logs/"
        if not os.path.exists(path):
            os.mkdir(path)
        self.load_app_config()

    def load_app_config(self):
        config = QtCore.QFile(":app_config")
        config.open(QtCore.QFile.ReadOnly)
        et = ET.fromstring(config.readAll().data())
        node = et.find("connectionString10")
        self.server = node.get("server")
        self.port = node.get("port")
        self.user = node.get("user")
        self.password = node.get("password")
        self.database = node.get("database")

    def closeEvent(self, q_close_event):
        self.parent.show()
